﻿USE [aspnetdb]
GO

/****** Object:  StoredProcedure [dbo].[mybet_joinContest]    Script Date: 03/01/2011 22:40:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Franck Allion
-- Create date: 26/02/11
-- Description:	User join a contest
-- =============================================
CREATE PROCEDURE [dbo].[mybet_joinContest]
	@UserId             uniqueidentifier,
	@CustomContestId    int
AS
BEGIN

    DECLARE @TranStarted   bit
    SET @TranStarted = 0
   
    DECLARE @ErrorCode   int
    SET @ErrorCode = 0
    
    DECLARE @RoleId uniqueidentifier
    SELECT @RoleId = NULL
    
    IF( @@TRANCOUNT = 0 )
		BEGIN
		    BEGIN TRANSACTION
			SET @TranStarted = 1
		END
    ELSE
		BEGIN
			SET @ErrorCode = -1
    		GOTO Cleanup
		END
		
    -- Insert new subscription
    DELETE Subscriptions 
		WHERE UserId=@UserId AND CustomContestId=@CustomContestId;
    
	INSERT Subscriptions (UserId, CustomContestId)
	VALUES (@UserId,@CustomContestId);
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -2
		GOTO Cleanup
    END
    
    INSERT Ranks (RankId, EventCount, GoodPredictionCount, GoodScoreCount,
    MatchCount, SubscriptionId, TotalScore, CurrentRank)
    VALUES (0,0,0,0,0,@@IDENTITY,0,0); 
    
  	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -3
		GOTO Cleanup
    END  
    
    -- Delete user role
	DELETE aspnet_UsersInRoles 
		WHERE UserId=@UserId
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -4
		GOTO Cleanup
    END		

	-- Give player role
	-- Get Player role id
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE RoleName = 'Player'
    IF ( @RoleId IS NULL )
    BEGIN
 		SET @ErrorCode = -5
		GOTO Cleanup       
    END

    -- Add role	
	INSERT INTO aspnet_UsersInRoles  
	( UserId, 
	  RoleId )
	VALUES
	( @UserId, @RoleId)
	
	IF( @@ERROR <> 0 )
    BEGIN
		SET @ErrorCode = -6
		GOTO Cleanup
    END
	
    -- commit transaction
    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END
    
    SET @ErrorCode = 0
    
    SET ROWCOUNT 1
    
    RETURN 0
    
Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    
    SET ROWCOUNT 0
    
	RETURN @ErrorCode    
    
END

GO


